import os
import sys
import xlsxwriter  # 批量写入表
import xlrd  # 打开表
import re

fileList = []
# pathList = r'C:\Users\Seven\Desktop\新建文件夹 (3)'

def filePath (pathList):

    for root, dir, files in os.walk(pathList):
        for file in files:
            filename = os.path.join(root, file)
            filename = filename.strip()
            if 'xls' in filename:
                print(filename)
                fileList.append(filename)

def writeFile(path, data):
    path = path + '/res.txt'
    with open(path, 'a', encoding='utf-8') as f:
        # for writedata in data:
        f.write(data)
        f.write('\n')
        f.write('\n')

def writeFileLog(path, data):
    path = path + '/log.txt'
    with open(path, 'a', encoding='utf-8') as f:
        # for writedata in data:
        f.write(data)
        f.write('\n')
        f.write('\n')



# 导入表，将excel表的数据变成要处理的数据形式。
def writeFilee(rpath):
    book = xlrd.open_workbook(rpath)  # 打开需要操作的excel表
    sheet = book.sheet_by_index(0)  # 根据顺序获取sheet
    txtList = []  # 取出文件内数据
    dataList2 = []  # 生成文件数据
    dataList2.append([rpath])
    for i in sheet.get_rows():
        txtList.append(i)

    for j in txtList:
        dataList1 = []
        for k in j:
            k = re.sub("'", "", str(k))
            a, b = k.strip().split(':')
            dataList1.append(b)
        dataList2.append(dataList1)
    return dataList2[:5]


def Wfile(listOut, rpath):
    rpath = rpath.split('.')[0] + '.xlsx'
    workbook = xlsxwriter.Workbook(rpath, {'constant_memory': True})
    worksheet = workbook.add_worksheet(name='导出')  # 表单名称

    for line in enumerate(listOut):
        n, value = line[0], line[1]
        for i in range(len(value)):
            v = [value[i]]
            worksheet.write_row(n + 1, i, v, cell_format=None)
    workbook.close()
    print('导出表生成成功' + rpath)

if __name__ == '__main__':
    pathList = input('文件夹路径：\n')
    filePath(pathList)
    for i in fileList:
        try:
            data = writeFilee(i)
            writeFile(pathList, i)
            for j in data[1:]:
                j = str(j)
                writeFile(pathList, j)
        except:
            writeFileLog(pathList, i)
    import time
    time.sleep(10)

    # Wfile(data, r'C:\Users\Seven\Desktop\新建文件夹 (3)')